package com.example.oa;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

@WebServlet("/employee")
public class EmployeeServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String action = req.getParameter("action");
        if (action.equals("sign_up")) {
            signUp(req, resp);
        }
        if (action.equals("sign_in")) {
            signIn(req, resp);
        }
        if (action.equals("add")) {
            add(req, resp);
        }
        if (action.equals("findById")) {
            findById(req, resp);
        }
        if (action.equals("update")) {
            update(req, resp);
        }
        if (action.equals("sign_out")) {
            signOut(req, resp);
    }
        if (action.equals("removeById")) {
            removeById(req, resp);
        }
        if (action.equals("detail")) {
            detail(req, resp);
        }
    }


    protected void detail(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));

        Connection connection = DB.getConnection();
        String sql = "select * from db_oa.employee where id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            resultSet.next();

            String name = resultSet.getString("name");
            String password = resultSet.getString("password");
            int age = resultSet.getInt("age");
            String gender = resultSet.getString("gender");
            int role = resultSet.getInt("role");

            Employee employee = new Employee(id, name, password, age, gender, role);

            req.getSession().setAttribute("employee", employee);

            resp.sendRedirect("admin/detail.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(resultSet, preparedStatement, connection);
        }
    }
        protected void removeById(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        Connection connection = DB.getConnection();
        String sql = "delete from db_oa.employee where id = ?";
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();

            req.getSession().setAttribute("employees", getAllEmployees());

            resp.sendRedirect("admin/admin.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(null, preparedStatement, connection);
        }
    }
        protected void signOut(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {
        req.getSession().invalidate();
        resp.sendRedirect("index.jsp");
    }
        protected void update(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        String name = req.getParameter("name");
        int age = Integer.parseInt(req.getParameter("age"));

        Connection connection = DB.getConnection();
        String sql = "update db_oa.employee set name = ?, age = ? where id = ?";
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.setInt(3, id);

            preparedStatement.executeUpdate();

            req.getSession().setAttribute("employees", getAllEmployees());
            resp.sendRedirect("admin/admin.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DB.close(null,preparedStatement,connection);
        }
    }
        protected void findById(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        Connection connection = DB.getConnection();
        String sql = "select * from db_oa.employee where id = ? ";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();

            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String gender = resultSet.getString("gender");
            Employee employee = new Employee(id,name,age,gender);

            req.getSession().setAttribute("employee",employee);
            resp.sendRedirect("admin/edit.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DB.close(resultSet,preparedStatement,connection);
        }
    }
        protected void add(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {
            String name = req.getParameter("name");
            String password = req.getParameter("password");
            int age = Integer.parseInt(req.getParameter("age"));
            String gender = req.getParameter("gender");

            Connection connection = DB.getConnection();
            String sql = "insert into db_oa.employee(name,password,age,gender) values(?,?,?,?)";
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;

            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,name);
                preparedStatement.setString(2,password);
                preparedStatement.setInt(3,age);
                preparedStatement.setString(4,gender);

                preparedStatement.executeUpdate();

                req.getSession().setAttribute("emmployees",getAllEmployees());

                resp.sendRedirect("admin/admin.jsp");
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                DB.close(resultSet,preparedStatement,connection);
            }

        }

        private ArrayList<Employee>getAllEmployees(){
            Connection connection = DB.getConnection();
            String sqlList = "select * from db_oa.employee where role <>1";
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;

            try {
                preparedStatement = connection.prepareStatement(sqlList);
                resultSet = preparedStatement.executeQuery();
                ArrayList<Employee> employees = new ArrayList<>();
                while(resultSet.next()) {
                    int idDB = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    int age = resultSet.getInt("age");
                    String gender = resultSet.getString("gender");
                    Employee employee = new Employee(idDB, name, age, gender);
                    employees.add(employee);
                }
                return employees;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }
        protected void signIn(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        String password = req.getParameter("password");

        Connection connection = DB.getConnection();
        String sql = "select * from db_oa.employee where id = ? and password = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.setString(2,password);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                int role = resultSet.getInt("role");
                if (role == 0){
                    String name = resultSet.getString("name");
                    int age = Integer.parseInt(resultSet.getString("age"));
                    String gender = resultSet.getString("gender");
                    Employee employee = new Employee(id,name,age,gender);
                    req.getSession().setAttribute("name",name);
                    req.getSession().setAttribute("role",role);
                    req.getSession().setAttribute("employee",employee);
                    resp.sendRedirect("home.jsp");
                }
                if (role == 1){
                    req.getSession().setAttribute("employees",getAllEmployees());
                    resp.sendRedirect("admin/admin.jsp");
                }
                return ;
            }
            req.setAttribute("error","Invalid id or password");
            req.getRequestDispatcher("index.jsp").forward(req,resp);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DB.close(resultSet,preparedStatement,connection);
        }
    }
    protected void signUp(HttpServletRequest req, HttpServletResponse resp) {
        String name = req.getParameter("name");
        String password = req.getParameter("password");
        int age = Integer.parseInt(req.getParameter("age"));
        String gender = req.getParameter("gender");

        Connection connection = DB.getConnection();
        String sql = "insert into db_oa.employee (name, password, age, gender) values (?,?,?,?)";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, password);
            preparedStatement.setInt(3, age);
            preparedStatement.setString(4, gender);

            preparedStatement.executeUpdate();

            preparedStatement = connection.prepareStatement("select last_insert_id();");
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            int id = resultSet.getInt(1);

            resp.sendRedirect("index.jsp");

            req.getSession().setAttribute("message", "Sign up successful， your ID is :" + id);
        } catch (SQLException | IOException e) {
            throw new RuntimeException(e);
        } finally {
            DB.close(resultSet, preparedStatement, connection);
        }
    }

}






